﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapGSCOLOR(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveGSCOLOR(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryGSCOLOR(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapGSCOLOR(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertGSCOLOR(dwLocal);
            }
            UpdateGSCOLOR(dwLocal);
        }
        public void InsertGSCOLOR(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            //GSCMANDANT  GSCART GSCVAR GSCFARBE
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO GSCOLOR "
                + "              (GSCMANDANT, GSCART, GSCVAR, GSCFARBE, GSCAENDZL, GSCAENDBEN, GSCAENDDAT, GSCNEUBEN, GSCNEUDAT, GSCSTATUS, GSCSFKZ, GSCBEZ, GSCFKARTNR, GSCKTFARBE, GSCBOM, GSCBOMCGRP, GSCFDVF, GSCVORB, GSCNACHB, GSCFA, GSCRUESTGRP, GSCPRUEFGRP, GSCSTATVF, GSCKZDUMCOL, GSCPSEUDOCOL, GSCTEXTSCHL, GSCMUFRB, GSCSCHLIAUF, GSCFIXTEMP, GSCFIXTIME, GSCABRASION, GSCABRAMTR, GSCPROGRP, GSCARTGRP, GSCRECHNUM, GSCRECHNR, GSCPRGRP, GSCPREIS, GSCZUAK, GSCZUPROZ, GSCANFART, GSCWERT, GSCWERTPROZ, GSCZUAB, GSCZUABPROZ, GSCZTWAHL, GSCVERLUST, GSCKALKMG, GSCLOAN, GSCLBAN, GSCLPAN, GSCPKL, GSCMU, GSCMURLOD, GSCMUPROD, GSCMUCTOD, GSCMUBIRL, GSCMUDTRL, GSCCOLPT1, GSCCOLPT2, GSCCOLPT3, GSCCOLPT4, GSCFILLER)"
                + "VALUES (:GSCMANDANT, :GSCART, :GSCVAR,:GSCFARBE, 0,                   '        ',                 0,                      'INTEXE  ', 0                  ,   'A',                'N', :GSCBEZ, '          ', '          ', ' ', ' ', '   ', '   ', '   ', 0, '   ', ' ', '   ', ' ', '          ', '          ', '          ', 0, 0, 0, 0, 0, '   ', '   ', '    ', 0, ' ', 0, 0, ' ', '    ', 0, ' ', 0, ' ', 0, 0, 0, '  ', '  ', '        ', '  ', ' ', '00', '00', ' ', '        ', 0, ' ', ' ', ' ', ' ', '                                                    ')";
            or1.Parameters.Add("GSCMANDANT", OracleType.NVarChar).Value = dwData["GSCMANDANT"].ToString();
            or1.Parameters.Add("GSCART", OracleType.NVarChar).Value = dwData["GSCART"].ToString();
            or1.Parameters.Add("GSCVAR", OracleType.NVarChar).Value = dwData["GSCVAR"].ToString();
            or1.Parameters.Add("GSCFARBE", OracleType.NVarChar).Value = dwData["GSCFARBE"].ToString();
            or1.Parameters.Add("GSCBEZ", OracleType.NVarChar).Value = dwData["GSCBEZ"].ToString();
            setResult = Connect2Proxy(or1);
        }
        public DataSet QueryGSCOLOR(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            // GSCMANDANT GSCART GSCVAR GSCFARBE
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0: 
                    or1.CommandText = "SELECT * FROM GSCOLOR WHERE  ROWNUM<2 "; 
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM GSCOLOR WHERE  "
                    + "  GSCMANDANT=:GSCMANDANT AND GSCART=:GSCART AND GSCVAR=:GSCVAR AND GSCFARBE=:GSCFARBE  ";
                    or1.Parameters.Add("GSCMANDANT", OracleType.NVarChar).Value = dwData["GSCMANDANT"].ToString();
                    or1.Parameters.Add("GSCART", OracleType.NVarChar).Value = dwData["GSCART"].ToString();
                    or1.Parameters.Add("GSCVAR", OracleType.NVarChar).Value = dwData["GSCVAR"].ToString();
                    or1.Parameters.Add("GSCFARBE", OracleType.NVarChar).Value = dwData["GSCFARBE"].ToString(); ;
                    break;
                case 3:
                    or1.CommandText = "SELECT DISTINCT GS.* FROM EKPOS GA LEFT JOIN GSCOLOR GS ON EKPMANDANT=GSCMANDANT "
                        + "  AND  utl_raw.cast_to_varchar2(EKPART)=GSCART AND  utl_raw.cast_to_varchar2(EKPVAR)=GSCVAR AND utl_raw.cast_to_varchar2(EKPFARBE)=GSCFARBE "
                        + "  WHERE EKPSA='PO' AND EKPARTGR=1 AND EKPMANDANT=:EKPMANDANT AND  EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
                case 4:
                    or1.CommandText = "  SELECT DISTINCT GS.* FROM GAAUF GA RIGHT JOIN GSCOLOR GS ON GAMANDANT=GSCMANDANT"
                        + " AND GAART=GSCART AND GAVAR=GSCVAR AND GAFARBE=GSCFARBE "
                        + "  WHERE GAMANDANT=:GAMANDANT AND  GAAUFKRS=:GAAUFKRS AND GAAUFLFD=:GAAUFLFD ";
                    or1.Parameters.Add("GAMANDANT", OracleType.NVarChar).Value = dwData["GAMANDANT"].ToString();
                    or1.Parameters.Add("GAAUFKRS", OracleType.NVarChar).Value = dwData["GAAUFKRS"].ToString();
                    or1.Parameters.Add("GAAUFLFD", OracleType.NVarChar).Value = dwData["GAAUFLFD"].ToString();
                    break;
                case 5:
                    or1.CommandText = "SELECT DISTINCT GS.* FROM GSCOLOR GS JOIN GSDAT ON GSART=GSCART AND GSVAR=GSCVAR   "
                        + "  WHERE GSCART=:GSCART AND GSCVAR=:GSCVAR AND GSCFARBE=:GSCFARBE ";
                    or1.Parameters.Add("GSCART", OracleType.NVarChar).Value = dwData["GAART"].ToString();
                    or1.Parameters.Add("GSCVAR", OracleType.NVarChar).Value = dwData["GAVAR"].ToString();
                    or1.Parameters.Add("GSCFARBE", OracleType.NVarChar).Value = dwData["GAFARBE"].ToString(); 
                    break;
                case 6:
                    or1.CommandText = "  SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
                    + " JOIN GSCOLOR M ON  GSCART=GAART AND GSCVAR=GAVAR AND GSCFARBE=GAFARBE "
                        + "WHERE VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD ";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    break;
                case 7:
                    or1.CommandText = "SELECT MAX(GSCVAR) AS GSCVAR FROM GSCOLOR WHERE  "
                    + "   GSCART=:GSCART  AND GSCFARBE=:GSCFARBE  ";
                    or1.Parameters.Add("GSCART", OracleType.NVarChar).Value = dwData["GAGGART"].ToString(); 
                    or1.Parameters.Add("GSCFARBE", OracleType.NVarChar).Value = dwData["GAGGFARBE"].ToString(); ;
                    break;
                case 8:
                    or1.CommandText = "  SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
                    + " JOIN GSCOLOR M ON  GSCART=GAART AND GSCVAR=GAVAR AND GSCFARBE=GAFARBE "
                        + "WHERE VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD AND VKPAUFPOS=:VKPAUFPOS ";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    or1.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwData["VKPAUFPOS"].ToString();
                    break;                    
                case 2:
                default:
                    or1.CommandText = "SELECT * FROM GSCOLOR WHERE  "
                    + "  GSCMANDANT=:GSCMANDANT AND GSCART=:GSCART AND GSCVAR=:GSCVAR AND GSCFARBE=:GSCFARBE  ";
                    or1.Parameters.Add("GSCMANDANT", OracleType.NVarChar).Value = dwData["GSCMANDANT"].ToString();
                    or1.Parameters.Add("GSCART", OracleType.NVarChar).Value = dwData["GSCART"].ToString();
                    or1.Parameters.Add("GSCVAR", OracleType.NVarChar).Value = dwData["GSCVAR"].ToString();
                    or1.Parameters.Add("GSCFARBE", OracleType.NVarChar).Value = dwData["GSCFARBE"].ToString(); ;

                    break;
            }

            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "GSCOLOR";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
        public void UpdateGSCOLOR(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE GSCOLOR SET "
                + "GSCMANDANT=:GSCMANDANT,"
                + "GSCART=:GSCART,"
                + "GSCVAR=:GSCVAR,"
                + "GSCFARBE=:GSCFARBE,"
                + "GSCAENDZL=:GSCAENDZL,"
                + "GSCAENDBEN=:GSCAENDBEN,"
                + "GSCAENDDAT=:GSCAENDDAT,"
                + "GSCNEUBEN=:GSCNEUBEN,"
                + "GSCNEUDAT=:GSCNEUDAT,"
                + "GSCSTATUS=:GSCSTATUS,"
                + "GSCSFKZ=:GSCSFKZ,"
                + "GSCBEZ=:GSCBEZ,"
                + "GSCFKARTNR=:GSCFKARTNR,"
                + "GSCKTFARBE=:GSCKTFARBE,"
                + "GSCBOM=:GSCBOM,"
                + "GSCBOMCGRP=:GSCBOMCGRP,"
                + "GSCFDVF=:GSCFDVF,"
                + "GSCVORB=:GSCVORB,"
                + "GSCNACHB=:GSCNACHB,"
                + "GSCFA=:GSCFA,"
                + "GSCRUESTGRP=:GSCRUESTGRP,"
                + "GSCPRUEFGRP=:GSCPRUEFGRP,"
                + "GSCSTATVF=:GSCSTATVF,"
                + "GSCKZDUMCOL=:GSCKZDUMCOL,"
                + "GSCPSEUDOCOL=:GSCPSEUDOCOL,"
                + "GSCTEXTSCHL=:GSCTEXTSCHL,"
                + "GSCMUFRB=:GSCMUFRB,"
                + "GSCSCHLIAUF=:GSCSCHLIAUF,"
                + "GSCFIXTEMP=:GSCFIXTEMP,"
                + "GSCFIXTIME=:GSCFIXTIME,"
                + "GSCABRASION=:GSCABRASION,"
                + "GSCABRAMTR=:GSCABRAMTR,"
                + "GSCPROGRP=:GSCPROGRP,"
                + "GSCARTGRP=:GSCARTGRP,"
                + "GSCRECHNUM=:GSCRECHNUM,"
                + "GSCRECHNR=:GSCRECHNR,"
                + "GSCPRGRP=:GSCPRGRP,"
                + "GSCPREIS=:GSCPREIS,"
                + "GSCZUAK=:GSCZUAK,"
                + "GSCZUPROZ=:GSCZUPROZ,"
                + "GSCANFART=:GSCANFART,"
                + "GSCWERT=:GSCWERT,"
                + "GSCWERTPROZ=:GSCWERTPROZ,"
                + "GSCZUAB=:GSCZUAB,"
                + "GSCZUABPROZ=:GSCZUABPROZ,"
                + "GSCZTWAHL=:GSCZTWAHL,"
                + "GSCVERLUST=:GSCVERLUST,"
                + "GSCKALKMG=:GSCKALKMG,"
                + "GSCLOAN=:GSCLOAN,"
                + "GSCLBAN=:GSCLBAN,"
                + "GSCLPAN=:GSCLPAN,"
                + "GSCPKL=:GSCPKL,"
                + "GSCMU=:GSCMU,"
                + "GSCMURLOD=:GSCMURLOD,"
                + "GSCMUPROD=:GSCMUPROD,"
                + "GSCMUCTOD=:GSCMUCTOD,"
                + "GSCMUBIRL=:GSCMUBIRL,"
                + "GSCMUDTRL=:GSCMUDTRL,"
                + "GSCCOLPT1=:GSCCOLPT1,"
                + "GSCCOLPT2=:GSCCOLPT2,"
                + "GSCCOLPT3=:GSCCOLPT3,"
                + "GSCCOLPT4=:GSCCOLPT4,"
                + "GSCFILLER=:GSCFILLER"
                + " WHERE  "
                + "  GSCMANDANT=:GSCMANDANT AND GSCART=:GSCART  AND GSCVAR=:GSCVAR  AND GSCFARBE=:GSCFARBE";
            or1.Parameters.Add("GSCMANDANT", OracleType.NVarChar).Value = dwData["GSCMANDANT"].ToString();
            or1.Parameters.Add("GSCART", OracleType.NVarChar).Value = dwData["GSCART"].ToString();
            or1.Parameters.Add("GSCVAR", OracleType.NVarChar).Value = dwData["GSCVAR"].ToString();
            or1.Parameters.Add("GSCFARBE", OracleType.NVarChar).Value = dwData["GSCFARBE"].ToString();
            or1.Parameters.Add("GSCAENDZL", OracleType.NVarChar).Value = dwData["GSCAENDZL"].ToString();
            or1.Parameters.Add("GSCAENDBEN", OracleType.NVarChar).Value = dwData["GSCAENDBEN"].ToString();
            or1.Parameters.Add("GSCAENDDAT", OracleType.NVarChar).Value = dwData["GSCAENDDAT"].ToString();
            or1.Parameters.Add("GSCNEUBEN", OracleType.NVarChar).Value = dwData["GSCNEUBEN"].ToString();
            or1.Parameters.Add("GSCNEUDAT", OracleType.NVarChar).Value = dwData["GSCNEUDAT"].ToString();
            or1.Parameters.Add("GSCSTATUS", OracleType.NVarChar).Value = dwData["GSCSTATUS"].ToString();
            or1.Parameters.Add("GSCSFKZ", OracleType.NVarChar).Value = dwData["GSCSFKZ"].ToString();
            or1.Parameters.Add("GSCBEZ", OracleType.NVarChar).Value = dwData["GSCBEZ"].ToString();
            or1.Parameters.Add("GSCFKARTNR", OracleType.NVarChar).Value = dwData["GSCFKARTNR"].ToString();
            or1.Parameters.Add("GSCKTFARBE", OracleType.NVarChar).Value = dwData["GSCKTFARBE"].ToString();
            or1.Parameters.Add("GSCBOM", OracleType.NVarChar).Value = dwData["GSCBOM"].ToString();
            or1.Parameters.Add("GSCBOMCGRP", OracleType.NVarChar).Value = dwData["GSCBOMCGRP"].ToString();
            or1.Parameters.Add("GSCFDVF", OracleType.NVarChar).Value = dwData["GSCFDVF"].ToString();
            or1.Parameters.Add("GSCVORB", OracleType.NVarChar).Value = dwData["GSCVORB"].ToString();
            or1.Parameters.Add("GSCNACHB", OracleType.NVarChar).Value = dwData["GSCNACHB"].ToString();
            or1.Parameters.Add("GSCFA", OracleType.NVarChar).Value = dwData["GSCFA"].ToString();
            or1.Parameters.Add("GSCRUESTGRP", OracleType.NVarChar).Value = dwData["GSCRUESTGRP"].ToString();
            or1.Parameters.Add("GSCPRUEFGRP", OracleType.NVarChar).Value = dwData["GSCPRUEFGRP"].ToString();
            or1.Parameters.Add("GSCSTATVF", OracleType.NVarChar).Value = dwData["GSCSTATVF"].ToString();
            or1.Parameters.Add("GSCKZDUMCOL", OracleType.NVarChar).Value = dwData["GSCKZDUMCOL"].ToString();
            or1.Parameters.Add("GSCPSEUDOCOL", OracleType.NVarChar).Value = dwData["GSCPSEUDOCOL"].ToString();
            or1.Parameters.Add("GSCTEXTSCHL", OracleType.NVarChar).Value = dwData["GSCTEXTSCHL"].ToString();
            or1.Parameters.Add("GSCMUFRB", OracleType.NVarChar).Value = dwData["GSCMUFRB"].ToString();
            or1.Parameters.Add("GSCSCHLIAUF", OracleType.NVarChar).Value = dwData["GSCSCHLIAUF"].ToString();
            or1.Parameters.Add("GSCFIXTEMP", OracleType.NVarChar).Value = dwData["GSCFIXTEMP"].ToString();
            or1.Parameters.Add("GSCFIXTIME", OracleType.NVarChar).Value = dwData["GSCFIXTIME"].ToString();
            or1.Parameters.Add("GSCABRASION", OracleType.NVarChar).Value = dwData["GSCABRASION"].ToString();
            or1.Parameters.Add("GSCABRAMTR", OracleType.NVarChar).Value = dwData["GSCABRAMTR"].ToString();
            or1.Parameters.Add("GSCPROGRP", OracleType.NVarChar).Value = dwData["GSCPROGRP"].ToString();
            or1.Parameters.Add("GSCARTGRP", OracleType.NVarChar).Value = dwData["GSCARTGRP"].ToString();
            or1.Parameters.Add("GSCRECHNUM", OracleType.NVarChar).Value = dwData["GSCRECHNUM"].ToString();
            or1.Parameters.Add("GSCRECHNR", OracleType.NVarChar).Value = dwData["GSCRECHNR"].ToString();
            or1.Parameters.Add("GSCPRGRP", OracleType.NVarChar).Value = dwData["GSCPRGRP"].ToString();
            or1.Parameters.Add("GSCPREIS", OracleType.NVarChar).Value = dwData["GSCPREIS"].ToString();
            or1.Parameters.Add("GSCZUAK", OracleType.NVarChar).Value = dwData["GSCZUAK"].ToString();
            or1.Parameters.Add("GSCZUPROZ", OracleType.NVarChar).Value = dwData["GSCZUPROZ"].ToString();
            or1.Parameters.Add("GSCANFART", OracleType.NVarChar).Value = dwData["GSCANFART"].ToString();
            or1.Parameters.Add("GSCWERT", OracleType.NVarChar).Value = dwData["GSCWERT"].ToString();
            or1.Parameters.Add("GSCWERTPROZ", OracleType.NVarChar).Value = dwData["GSCWERTPROZ"].ToString();
            or1.Parameters.Add("GSCZUAB", OracleType.NVarChar).Value = dwData["GSCZUAB"].ToString();
            or1.Parameters.Add("GSCZUABPROZ", OracleType.NVarChar).Value = dwData["GSCZUABPROZ"].ToString();
            or1.Parameters.Add("GSCZTWAHL", OracleType.NVarChar).Value = dwData["GSCZTWAHL"].ToString();
            or1.Parameters.Add("GSCVERLUST", OracleType.NVarChar).Value = dwData["GSCVERLUST"].ToString();
            or1.Parameters.Add("GSCKALKMG", OracleType.NVarChar).Value = dwData["GSCKALKMG"].ToString();
            or1.Parameters.Add("GSCLOAN", OracleType.NVarChar).Value = dwData["GSCLOAN"].ToString();
            or1.Parameters.Add("GSCLBAN", OracleType.NVarChar).Value = dwData["GSCLBAN"].ToString();
            or1.Parameters.Add("GSCLPAN", OracleType.NVarChar).Value = dwData["GSCLPAN"].ToString();
            or1.Parameters.Add("GSCPKL", OracleType.NVarChar).Value = dwData["GSCPKL"].ToString();
            or1.Parameters.Add("GSCMU", OracleType.NVarChar).Value = dwData["GSCMU"].ToString();
            or1.Parameters.Add("GSCMURLOD", OracleType.NVarChar).Value = dwData["GSCMURLOD"].ToString();
            or1.Parameters.Add("GSCMUPROD", OracleType.NVarChar).Value = dwData["GSCMUPROD"].ToString();
            or1.Parameters.Add("GSCMUCTOD", OracleType.NVarChar).Value = dwData["GSCMUCTOD"].ToString();
            or1.Parameters.Add("GSCMUBIRL", OracleType.NVarChar).Value = dwData["GSCMUBIRL"].ToString();
            or1.Parameters.Add("GSCMUDTRL", OracleType.NVarChar).Value = dwData["GSCMUDTRL"].ToString();
            or1.Parameters.Add("GSCCOLPT1", OracleType.NVarChar).Value = dwData["GSCCOLPT1"].ToString();
            or1.Parameters.Add("GSCCOLPT2", OracleType.NVarChar).Value = dwData["GSCCOLPT2"].ToString();
            or1.Parameters.Add("GSCCOLPT3", OracleType.NVarChar).Value = dwData["GSCCOLPT3"].ToString();
            or1.Parameters.Add("GSCCOLPT4", OracleType.NVarChar).Value = dwData["GSCCOLPT4"].ToString();
            or1.Parameters.Add("GSCFILLER", OracleType.NVarChar).Value = dwData["GSCFILLER"].ToString();
            Connect2Proxy(or1);
        }
    }
}
